#include "dbtool.h"

#include "singletonroot.h"

DBTool::DBTool(QObject *parent) : QObject(parent)
{
    open();
}

DBTool::~DBTool()
{
    if(db.isOpen()){
        db.close();
    }
}

void DBTool::test()
{

//    auto info  = getTableInfo("classes");
    QSqlQuery u;
    deleteTableInfo("userInfo","class_id = 20260302",&u);


//    qDebug()<< info ;
//    auto info = getCreateClassInfo();

//    qDebug()<<info.counselors.count();
}

bool DBTool::log(DB_userInfo info)
{
    QString sql = R"(SELECT pwd,name FROM userInfo  WHERE account = %1 AND %2 = 1 ;
)";

    sql = sql.arg(info.account).arg(UserTypeStr[info.userType]);

    qDebug().noquote()<<sql;

    QSqlQuery query(sql);
    if(query.next()){
        QString pwd = query.value("pwd").toString();
        if(pwd == info.pwd){
            GDATA->name = query.value("name").toString();
            GDATA->Account = info.account;
            GDATA->Password = info.pwd;
            GDATA->userType = info.userType;

            return true;
        }else{
            errString = "账户或密码错误";
            return false;
        }

    }else{
        errString = "账户不存在或无权限";
        qDebug()<<"query err" << query.lastError().text();
        qDebug()<<"sql" << sql;
        return false;
    }

    return false;

}

QList<DB_View_StudentInfo> DBTool::getAllStudentInfo(const QString where)
{
    QList<DB_View_StudentInfo> rData;
    QString sql = R"(SELECT * FROM studentInfo )";
    if(where.isEmpty() == false){
        sql += " WHERE ";
        sql += where;
    }
    QSqlQuery query(sql);
    while(query.next()){
        rData << query;
    }
    if(query.size() == -1){
        qDebug() << "SQL Error record: " << query.lastError().text();
        qDebug().noquote() << "sql: " << sql;
    }

    return rData;
}

QList<DB_View_StudentGrades> DBTool::getAllStudentGrades(const QString where)
{
    QList<DB_View_StudentGrades> rData;
    QString sql = R"(SELECT * FROM studentGrades )";
    if(where.isEmpty() == false){
        sql += " WHERE ";
        sql += where;
    }
    QSqlQuery query(sql);
    while(query.next()){
        rData << query;
    }
    if(query.size() == -1){
        qDebug() << "SQL Error record: " << query.lastError().text();
        qDebug().noquote() << "sql: " << sql;
    }

    return rData;
}

QList<DB_View_ClassInfo> DBTool::getAllClassInfo(const QString where)
{
    QList<DB_View_ClassInfo> rData;
    QString sql = R"(SELECT * FROM classInfo )";
    if(where.isEmpty() == false){
        sql += " WHERE ";
        sql += where;
    }
    QSqlQuery query(sql);
    while(query.next()){
        rData << query;
    }
    if(query.size() == -1){
        qDebug() << "SQL Error record: " << query.lastError().text();
        qDebug().noquote() << "sql: " << sql;
    }

    return rData;
}

QList<DB_userInfo> DBTool::getAllUserInfo(const QString where)
{
    QList<DB_userInfo> rData;
    QString sql = R"(SELECT * FROM userInfo )";
    if(where.isEmpty() == false){
        sql += " WHERE ";
        sql += where;
    }
    QSqlQuery query(sql);
    while(query.next()){
        rData << query;
    }
    if(query.size() == -1){
        qDebug() << "SQL Error record: " << query.lastError().text();
        qDebug().noquote() << "sql: " << sql;
    }

    return rData;
}

QList<QVariantList> DBTool::getTableInfo(const QString &table, const QString where)
{
    int colCount = getTableColCount(table);


    QList<QVariantList> rData;
    QString sql = R"(SELECT * FROM %1 )";
    sql = sql.arg(table);
    if(where.isEmpty() == false){
        sql += " WHERE ";
        sql += where;
    }
    QSqlQuery query(sql);
    while(query.next()){
        QVariantList aRow;
        for(int i = 0; i < colCount;i++){
            aRow << query.value(i);
        }
        rData << aRow;
    }
    if(query.size() == -1){
        qDebug() << "SQL Error record: " << query.lastError().text();
        qDebug().noquote() << "sql: " << sql;
    }

    return rData;
}

bool DBTool::insertTableInfo(const QString &table, const QList<QVariantList> &data, QSqlQuery *query)
{
    bool hasQuery = query;
    auto colInfo = getTableColInfo(table);

    QStringList colNamesStr,parmStr;
    for(auto&& col: colInfo){
        colNamesStr << col.name;
        parmStr << "?";
    }

    QString sql = R"(INSERT INTO %0 (
                        %1
                    )
                    VALUES (
                        %2
                    );
)";
    sql = sql.arg(table).arg(colNamesStr.join(",")).arg(parmStr.join(","));

    QSqlQuery queryTemp;
    if(hasQuery == false){
        query = &queryTemp;
    }

    if(!hasQuery){
        query->exec("begin"); // 开始事务
    }

    bool rData = false;
    for(auto aRow : data){
        query->prepare(sql);
        for(auto&& col: colInfo){
            query->bindValue(col.cid,aRow.at(col.cid));
        }
        rData = exec(*query);
    }

    if(!hasQuery){
        rData = query->exec("commit");
    }

    return rData;

}

bool DBTool::deleteTableInfo(const QString &table, const QString where, QSqlQuery *query)
{
    bool hasQuery = query;


    QString sql = R"(DELETE FROM %0 )";
    sql = sql.arg(table);
    if(where.isEmpty() == false){
        sql += " WHERE ";
        sql += where;
    }


    QSqlQuery queryTemp;
    if(hasQuery == false){
        query = &queryTemp;
    }

    if(!hasQuery){
        query->exec("begin"); // 开始事务
    }

    bool rData = false;

    query->prepare(sql);
    rData = exec(*query);


    if(!hasQuery){
        rData = query->exec("commit");
    }

    qDebug().noquote()<<"SQL::"<<sql;

    return rData;
}

bool DBTool::updateTableInfo(const QString &table, const QString &set, const QString where, QSqlQuery *query)
{
    bool hasQuery = query;


    QString sql = R"(UPDATE %0
   SET %1
 WHERE %2 ;
)";
    sql = sql.arg(table).arg(set).arg(where);

    QSqlQuery queryTemp;
    if(hasQuery == false){
        query = &queryTemp;
    }

    if(!hasQuery){
        query->exec("begin;"); // 开始事务
    }

    bool rData = false;

    query->prepare(sql);
    rData = exec(*query);


    if(!hasQuery){
        rData = query->exec("commit;");
    }

    return rData;
}

DB_CreateClassInfo DBTool::getCreateClassInfo()
{
    DB_CreateClassInfo info;
    QStringList tables = {"professionals","onlyTeacher","courses","onlyCounselor"};

    for(int i = 0 ; i < tables.count() ; i ++){
        auto tableName = tables.at(i);

        QList<DB_CreateClassInfo::T_Info>& rData = info.data(i);
        QString sql = R"(SELECT * FROM %1 )";
        sql = sql.arg(tableName);
//        if(where.isEmpty() == false){
//            sql += " WHERE ";
//            sql += where;
//        }
        QSqlQuery query(sql);
        while(query.next()){
            rData << query;
        }
        if(query.size() == -1){
            qDebug() << "SQL Error record: " << query.lastError().text();
            qDebug().noquote() << "sql: " << sql;
        }
    }

    return info;
}


void DBTool::open()
{
    db.setDatabaseName(GDATA->dbName); // 指定数据库文件路径
    if (!db.open()) {
        qDebug() << "Error: Failed to connect to database.";
        qDebug() << db.lastError().text();
    }else{
        qDebug()<<"Open Success: "<<GDATA->dbName;
    }
}

int DBTool::getTableColCount(QString tableName)
{
    return getTableColInfo(tableName).size();
}

QList<TableInfo> DBTool::getTableColInfo(QString tableName)
{
    QList<TableInfo> rData;

    QSqlQuery query;
    QString strTableNmae = tableName;
    QString sql = "PRAGMA table_info(" + strTableNmae + ")";
    query.prepare(sql);
    if (query.exec())
    {
        while (query.next())
        {
            rData << query;
            qDebug() << QString(QString("字段数:%1     字段名:%2     字段类型:%3")).arg(query.value(0).toString()).arg(query.value(1).toString()).arg(query.value(2).toString());
        }
    }else{
        qDebug() << query.lastError();
    }

    return rData;
}

bool DBTool::exec( QSqlQuery &query)
{
    if (!query.exec()) {
        // 处理错误
        qDebug() << "Error deleting record: " << query.lastError().text();
        qDebug().noquote() << "sql: " << query.executedQuery();
        return false;
    }
    else{
        qDebug().noquote() << "sql: " << query.executedQuery();
        return true;
    }
}
